﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Dados
    Private conexao As String
    Public con As SqlConnection

    Sub New()
        'conexão fora da Toledo
        'conexao = "data source=201.63.132.162,7775\MSSQLSERVER; initial catalog=Transportadora;User ID=transporte;Password=transtoledo;"
        'conexão dentro da Toledo
        conexao = "data source=10.0.0.9,1433\MSSQLSERVER; initial catalog=Transportadora;User ID=transporte;Password=transtoledo;"
        con = New SqlConnection(conexao)
    End Sub
    'abrir conexao
    Public Sub abrirConexao()
        Try
            con.Open()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    'fechar conexao
    Public Sub fecharConexao()
        Try
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    'update e delete, insert (sem retorno de codigo)
    Public Sub executacomando(ByVal cmd As SqlCommand)
        Try
            abrirconexao()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            fecharconexao()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    'insert que retornam o codigo
    Public Function executacomandoAI(ByVal cmd As SqlCommand, ByVal tabela As String) As Integer
        Dim codigo As Integer
        Try
            abrirconexao()
            cmd.Connection = con
            cmd.CommandText &= "; select @@identity " & tabela
            codigo = cmd.ExecuteScalar
            fecharconexao()
        Catch ex As Exception
            Throw ex
        End Try
        Return codigo
    End Function

    'select
    Function executaConsulta(ByVal cmd As SqlCommand) As DataTable
        Dim tab As New DataTable
        Dim da As New SqlDataAdapter
        Try
            cmd.Connection = con
            da.SelectCommand = cmd
            da.Fill(tab)
        Catch ex As Exception
            Throw ex
        End Try
        da = Nothing
        Return tab
    End Function

    Public Sub preencheCombo(ByVal combox As ComboBox, ByVal tabela As String, ByVal codigo As String, ByVal descricao As String, ByVal filtro As String, ByVal ordem As String)
        Dim cmd As SqlCommand
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Dim sql As String
        Try
            cmd = New SqlCommand()
            cmd.Connection = con
            sql = "select * from " & tabela
            If filtro <> "" Then
                sql = sql & " where " & filtro
            End If
            If ordem <> "" Then
                sql = sql & " order by " & ordem
            End If
            cmd.CommandText = sql
            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            'preenchendo o dataset
            da.Fill(ds, "tab")
            'associando a datatable ao combobox
            combox.DisplayMember = Nothing
            combox.ValueMember = Nothing
            combox.DataSource = Nothing
            combox.DataSource = ds.Tables(0)
            combox.DisplayMember = descricao
            combox.ValueMember = codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Function retdadosTab(ByVal cmd As SqlCommand) As DataTable
        Dim tab As DataTable
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Try
            cmd.Connection = con
            da = New SqlDataAdapter
            da.SelectCommand = cmd
            ds = New DataSet
            da.Fill(ds, "tab")
            tab = ds.Tables("tab")
        Catch ex As Exception
            Throw New Exception("Erro ao selecionar dados", ex)
        Finally
            da = Nothing
            ds = Nothing
        End Try
        Return tab
    End Function

    'verificação se existe no banco
    Public Function executacomandoString(ByVal cmd As SqlCommand, ByVal tabela As String) As Boolean
        Dim verifica As Boolean = False
        Dim read As SqlDataReader
        Try
            abrirconexao()
            cmd.Connection = con
            cmd.CommandText &= "; select @@identity " & tabela
            read = cmd.ExecuteReader()
            If (read.Read()) Then
                verifica = True
            End If
            fecharconexao()
        Catch ex As Exception
            Throw ex
        End Try
        Return verifica
    End Function
End Class
